package ua.epam.muzalevskiy.traction.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import ua.epam.muzalevskiy.traction.dao.exceptions.DatabaseException;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotExistsParameterException;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotUniqueParameterException;
import ua.epam.muzalevskiy.traction.route.Stop;
import ua.epam.muzalevskiy.traction.route.Route;
import org.apache.log4j.Logger;

/**
 *
 * @author Muzalevskiy Vitaliy
 */
public class StopDaoImplementation implements StopDao {

    private static final Logger LOGGER =
            Logger.getLogger(StopDaoImplementation.class);
    private final ConnectionFactory factory =
            ConnectionFactoryFactory.newConnectionFactory();
    
    /** Query for inserting new stop to DataBase.*/
    public static final String ADD_STOP_SQL = "INSERT INTO traction.stop "
            + "VALUES( ?, ?, ?, ?, ?, ?, ?)";
    
    /** Query to select all stops from DataBase.*/
    public static final String SELECT_ALL_SQL = "SELECT * FROM traction.stop";
    
    /** Query to select stop with concrete ID value.*/
    public static final String SELECT_BY_ID_SQL = "SELECT * FROM "
            + "traction.stop WHERE id = ?";
    
    /** Query to select stop by there coordinates*/
    public static final String SELECT_BY_COORDINATES_SQL = "SELECT * FROM "
            + "traction.stop WHERE latitude = ? AND longitude = ?";
    
    /** Query to select all stops of bus type from DataBase.*/
    public static final String SELECT_BUS_STOPS_SQL = "SELECT * FROM "
            + "traction.stop WHERE bus_type = true";
    
    /** Query to select all stops of trolleybus type from DataBase.*/
    public static final String SELECT_TROLLEYBUS_STOPS_SQL = "SELECT * FROM "
            + "traction.stop WHERE trolleybus_type = true";
    
    /** Query to select all stops of tram type from DataBase.*/
    public static final String SELECT_TRAM_STOPS_SQL = "SELECT * FROM "
            + "traction.stop WHERE tram_type = true";
    
    /** Query to delete stop with concrete ID value.(part 1)*/
    public static final String DELETE_BY_ID_SQL1 = "DELETE FROM traction.stop "
            + "WHERE id = ?";
    
    /** Query to delete stop with concrete ID value.(part 2)*/
    public static final String DELETE_BY_ID_SQL2 = "DELETE FROM "
            + "traction.routes_stops_conection WHERE stop_id = ?";
    
    /** Query to update stop type in DataBase.*/
    public static final String UPDATE_STOP_TYPE_SQL = "UPDATE traction.stop "
            + "SET bus_type = ?, trolleybus_type = ?, tram_type = ? WHERE id = ?";
    
    /** Query to select routes which goes thru current stop.*/
    public static final String SELECT_STOP_ROUTES_SQL = "SELECT route_id "
            + "FROM traction.routes_stops_conection WHERE stop_id = ?";
    
    public static final String ATTRIBUTE_ID = "id";
    public static final String ATTRIBUTE_STOP_NAME = "stop_name";
    public static final String ATTRIBUTE_BUS_TYPE = "bus_type";
    public static final String ATTRIBUTE_TROLLEYBUS_TYPE = "trolleybus_type";
    public static final String ATTRIBUTE_TRAM_TYPE = "tram_type";
    public static final String ATTRIBUTE_LATITUDE = "latitude";
    public static final String ATTRIBUTE_LONGITUDE = "longitude";

    static {
        ConnectionFactoryFactory.setType(ConnectionFactoryFactory.FactoryType.C3P0);
    }

    /**
     * Method need to save information about new stop in DataBase.
     * 
     * @param query <code>PreparedStatement</code> value that include all
     *      information that need to be saved in DataBase.
     * @throws SQLException if there was some problems with Query.
     */
    private void doInsert(PreparedStatement query, int stopID, String name,
            boolean busType, boolean trolleybusType, boolean tramType,
            double latitude, double longitude) throws SQLException {
        query.setInt(1, stopID);
        query.setString(2, name);
        query.setBoolean(3, busType);
        query.setBoolean(4, trolleybusType);
        query.setBoolean(5, tramType);
        query.setDouble(6, latitude);
        query.setDouble(7, longitude);
        query.executeUpdate();
        LOGGER.info("New Stop info was put to DataBase! (Stop_id = " + stopID + ")");
    }

    /**
     * Method for getting information about stop from DataBase.
     * 
     * @param result <code>ResultSet</code> value of concrete query.
     * @return <code>Stop</code> value.
     * @throws SQLException if there was some problems with Query.
     */
    private Stop createStope(ResultSet result) throws SQLException {
        int stopID = result.getInt(ATTRIBUTE_ID);
        String name = result.getString(ATTRIBUTE_STOP_NAME);
        boolean busType = result.getBoolean(ATTRIBUTE_BUS_TYPE);
        boolean trolleybusType = result.getBoolean(ATTRIBUTE_TROLLEYBUS_TYPE);
        boolean tramType = result.getBoolean(ATTRIBUTE_TRAM_TYPE);
        double latitude = result.getDouble(ATTRIBUTE_LATITUDE);
        double longitude = result.getDouble(ATTRIBUTE_LONGITUDE);
        LOGGER.debug("Stop info was taken from DataBase! (Stop_id = " + stopID + ")");
        return new Stop(stopID, name, busType, trolleybusType, tramType,
                latitude, longitude);
    }

    /**
     * Method need to get some list of stops.
     * @param sql <code>String</code> value of query with needed parameters.
     * @return <code>List</code> value with needed stops.
     */
    private List<Stop> selectListOfStops(String sql) {
        List<Stop> list = new LinkedList<Stop>();
        Stop stop;
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                Statement statement = myConnection.createStatement();
                ResultSet result = statement.executeQuery(sql);
                while (result.next()) {
                    stop = createStope(result);
                    list.add(stop);
                }

            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return list;
    }

    @Override
    public void addStop(Stop stop) throws NotUniqueParameterException {
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection.prepareStatement(ADD_STOP_SQL);

                myConnection.setAutoCommit(false);
                if (selectStopByID(stop.getId()) != null) {
                    throw new NotUniqueParameterException();
                }
                if (selectStopByCoordinates(stop.getLatitude(),
                        stop.getLongitude()) != null) {
                    throw new NotUniqueParameterException();
                }
                try {
                    doInsert(query, stop.getId(), stop.getName(), stop.isBusType(),
                            stop.isTrolleybusType(), stop.isTramType(),
                            stop.getLatitude(), stop.getLongitude());

                    myConnection.commit();
                } catch (SQLException ex) {
                    LOGGER.warn(ex.getMessage());
                    LOGGER.info("Trtansaction is rolled back!");
                    myConnection.rollback();
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public List<Stop> selectAll() {
        return selectListOfStops(SELECT_ALL_SQL);
    }

    @Override
    public Stop selectStopByID(int stopID) {
        Stop stop = null;
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_ID_SQL);
                query.setInt(1, stopID);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    stop = createStope(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return stop;
    }

    @Override
    public List<Stop> selectBusStops() {
        return selectListOfStops(SELECT_BUS_STOPS_SQL);
    }

    @Override
    public List<Stop> selectTrolleybusStops() {
        return selectListOfStops(SELECT_TROLLEYBUS_STOPS_SQL);
    }

    @Override
    public List<Stop> selectTramStops() {
        return selectListOfStops(SELECT_TRAM_STOPS_SQL);
    }

    @Override
    public void deleteStopByID(int stopID) throws NotExistsParameterException {
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query1 = myConnection
                        .prepareStatement(DELETE_BY_ID_SQL1);
                PreparedStatement query2 = myConnection
                        .prepareStatement(DELETE_BY_ID_SQL2);
                myConnection.setAutoCommit(false);
                Stop stop = selectStopByID(stopID);
                if (stop == null) {
                    throw new NotExistsParameterException();
                }
                query1.setInt(1, stopID);
                query1.executeUpdate();
                query2.setInt(1, stopID);
                query2.executeUpdate();
                query1.close();
                query2.close();
                myConnection.commit();
                LOGGER.info("Stop info was cleared from DataBase! (StopID = "
                        + stop.getId() + ")");
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public void changeStopType(int stopID, boolean busType,
            boolean trolleybusType, boolean tramType)
            throws DatabaseException {
        try {
            if (busType == false && trolleybusType == false && tramType == false) {
                throw new DatabaseException();
            }
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(UPDATE_STOP_TYPE_SQL);
                myConnection.setAutoCommit(false);
                query.setBoolean(1, busType);
                query.setBoolean(2, trolleybusType);
                query.setBoolean(3, tramType);
                query.setInt(4, stopID);
                query.executeUpdate();
                myConnection.commit();
                LOGGER.info("Stop info was changed! (StopID = " + stopID + ")");
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public List<Route> getStopRoutes(int stopID) {
        List<Route> list = new ArrayList<Route>();
        DaoFactory daoFactory = new DaoFactory();
        RouteDao routesDao = daoFactory.getRouteDao();
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_STOP_ROUTES_SQL);
                query.setInt(1, stopID);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    String routeNumber = result.getString(
                            RouteDaoImplementation.ATTRIBUTE_ROUTE_NUMBER);
                    list.add(routesDao.selectRouteByNumber(routeNumber));
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return list;
    }

    @Override
    public Stop selectStopByCoordinates(double latitude, double longitude) {
        Stop stop = null;
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_COORDINATES_SQL);
                query.setDouble(1, latitude);
                query.setDouble(2, longitude);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    stop = createStope(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return stop;
    }
}
